package biz.bbtec.sbm.store.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.store.bean.AddressBean;
import biz.bbtec.sbm.store.bean.CheckBean;
import biz.bbtec.sbm.store.bean.ItemBean;
import biz.bbtec.sbm.store.bean.StoreBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class StoreService {

	dbConnection db = new dbConnection();

	public List ShowAllStore(ItemBean item) {
		List<StoreBean> list = new ArrayList<StoreBean>();

		String sql1 = "select store.*,address.name as addname,address.description as adddesscription, "
				+ "item.unit as itemunit from store,address,item "
				+ "where item.itemid = store.itemid and store.adsid = address.adsid "
				+ "and store.itemid = ? ";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, item.getItemid());
			rs = past.executeQuery();
			while (rs.next()) {
				StoreBean store = new StoreBean();
				store.setStoreid(rs.getInt("storeid"));
				store.setAdsid(rs.getInt("adsid"));
				store.setAddname(rs.getString("addname"));
				store.setItemid(item.getItemid());
				store.setQty(Double.parseDouble(rs.getString("qty")));
				store.setUnitcost(Double.parseDouble(rs.getString("unitcost")));
				store.setPrice(store.getUnitcost() * store.getQty());
				store.setItemname(item.getName());
				store.setAdddescription(rs.getString("adddesscription"));
				store.setUnit(rs.getString("itemunit"));
				list.add(store);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List ShowAddAtore(AddressBean add) {
		List<StoreBean> list = new ArrayList<StoreBean>();

		String sql1 = "select store.*,item.name as itemname,item.description as itemdescription,item.unit as itemunit from "
				+ "item,store where store.itemid=item.itemid and store.adsid = ?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, add.getAdsid());
			rs = past.executeQuery();
			while (rs.next()) {
				StoreBean store = new StoreBean();
				store.setAdsid(add.getAdsid());
				store.setAddname(add.getName());
				store.setQty(Double.parseDouble(rs.getString("qty")));
				store.setUnitcost(Double.parseDouble(rs.getString("unitcost")));
				store.setPrice(store.getUnitcost() * store.getQty());
				store.setAdddescription(add.getDescription());
				store.setItemid(rs.getInt("itemid"));
				store.setItemname(rs.getString("itemname"));
				store.setItemdescription(rs.getString("itemdescription"));
				store.setUnit(rs.getString("itemunit"));
				list.add(store);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public StoreBean showonestore(int storeid) {
		StoreBean store = new StoreBean();

		String sql1 = "select *,item.name as itemname,item.unit as itemunit,address.name as addname from store,item,address"
				+ " where item.itemid = store.itemid and store.adsid = address.adsid and storeid=?";

		String sql2 = "select sum(qty) from store where itemid=? and adsid=?";
		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;

		try {
			ps1 = conn.prepareStatement(sql1);
			ps1.setInt(1, storeid);
			rs1 = ps1.executeQuery();
			if (rs1.next()) {
				store.setItemname(rs1.getString("itemname"));
				store.setItemid(rs1.getInt("itemid"));
				store.setUnit(rs1.getString("itemunit"));
				store.setAdsid(rs1.getInt("adsid"));
				store.setAddname(rs1.getString("addname"));
			} else {
				return null;
			}
			rs1.next();
			ps1.close();
			ps1 = conn.prepareStatement(sql2);
			ps1.setInt(1, store.getItemid());
			ps1.setInt(2, store.getAdsid());
			rs1 = ps1.executeQuery();
			if (rs1.next()) {
				store.setQty(rs1.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return store;
	}

	public List showalladdinfo(UserBean user, int adsid) {
		List<AddressBean> list = new ArrayList<AddressBean>();

		String sql1 = "select name,adsid from address where orgid=? and adsid <> ?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			past.setInt(2, adsid);
			rs = past.executeQuery();
			while (rs.next()) {
				AddressBean add = new AddressBean();
				add.setAdsid(rs.getInt("adsid"));
				add.setName(rs.getString("name"));
				list.add(add);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean modifystore(StoreBean store, int adsid) {
		boolean flag = false;
		double sum = store.getNewqty();
		String sql1 = "select * from store where itemid=? and adsid=? group by qty desc";

		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		ResultSet rs1 = null;

		try {
			ps1 = conn.prepareStatement(sql1);
			ps1.setInt(1, store.getItemid());
			ps1.setInt(2, store.getAdsid());
			rs1 = ps1.executeQuery();
			while ((rs1.next()) && (sum > 0)) {

				double qty = rs1.getDouble("qty");
				int storeid = rs1.getInt("storeid");
				double unitcost = rs1.getDouble("unitcost");

				if (qty > sum) {

					String sql3 = "insert into store(itemid,adsid,qty,unitcost) values("
							+ "'"
							+ store.getItemid()
							+ "'"
							+ ","
							+ "'"
							+ adsid
							+ "'"
							+ ","
							+ "'"
							+ sum
							+ "'"
							+ ","
							+ "'"
							+ unitcost + "'" + ")";

					ps2 = conn.prepareStatement(sql3);
					ps2.executeUpdate();
					ps2.close();

					sum = qty - sum;
					String sql2 = "update store set qty=? where storeid=?";
					ps2 = conn.prepareStatement(sql2);
					ps2.setDouble(1, sum);
					ps2.setInt(2, storeid);
					ps2.executeUpdate();

					break;
				} else {
					String sql2 = "update store set adsid=? where storeid=?";
					ps2 = conn.prepareStatement(sql2);
					ps2.setInt(1, adsid);
					ps2.setInt(2, storeid);
					ps2.executeUpdate();
					ps2.close();
					sum = sum - qty;
				}
			}
			rs1.close();
			ps1.close();

			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}
}
